/**
 * 
 */
package com.gametime.setup;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Random;

import com.gametime.util.DBUtils;



/**
 * @author dave
 *
 */



public class HeightGenerator {

	private static HeightGenerator instance=null;
	
	private HeightGenerator()
	{
	}
	
	public static HeightGenerator getInstance()
	{
		if(instance == null)
			instance = new HeightGenerator();
		return instance;
	}
	
	public void initHeightGenerator(Connection conn) throws Exception
	{
		 final String sqlPositions="select pos_id from position";
		 final String sqlHeights="select height_id from heights where pos_id = ?";
		 final String sqlUpdate ="update heights set seq=? where height_id=?";
		 ArrayList<Integer> positions= new ArrayList<Integer>();
		 ArrayList<Integer> primaryKeys= new ArrayList<Integer>();
		 
		 PreparedStatement ps =  conn.prepareStatement(sqlPositions);
		 ResultSet rs = ps.executeQuery();
		 while (rs.next())
		 {
			 positions.add(rs.getInt(1)); 
		 }
		 rs.close();
		 ps.clearParameters();
		 ps.close();
		 
		 for(int posId : positions)
		 {
			 primaryKeys = new ArrayList<Integer>();
			 ps =  conn.prepareStatement(sqlHeights);
			 ps.setInt(1, posId);
			 rs = ps.executeQuery();
			 while (rs.next())
			 {
				 primaryKeys.add(rs.getInt(1)); 
			 }
			 rs.close();
			 ps.clearParameters();
			 ps.close();
			 int count=1;
			 for(int i:primaryKeys)
			 {
				 ps =  conn.prepareStatement(sqlUpdate);
				 ps.setInt(1, count);
				 ps.setInt(2, i);
				 ps.executeUpdate();
				 ps.clearParameters();
				 ps.close();
				 count++;
				 
			 }
		 }
		 
	}
	
	public int getHeight(int posId, Connection conn) throws Exception
	{
		int maxRow = getMaxRows(posId, conn);
		Random r = new Random();
		int randRow = r.nextInt(maxRow) + 1;
		return getValue(posId, randRow, conn);
	}
	
	private int getValue(int posId, int randRow, Connection conn) throws Exception
	{
		int result=0;
		final String sql="select value from heights where pos_id = ? and seq=?";
		PreparedStatement ps =  conn.prepareStatement(sql);
		ps.setInt(1, posId);
		ps.setInt(2, randRow);
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			result= rs.getInt(1); 
		}
		rs.close();
		ps.clearParameters();
		ps.close();
		return result;
	}

	private int getMaxRows(int posId, Connection conn) throws Exception
	{
		int result=0;
		final String sql="select max(seq) from heights where pos_id = ?";
		PreparedStatement ps =  conn.prepareStatement(sql);
		ps.setInt(1, posId);
		ResultSet rs = ps.executeQuery();
		while (rs.next())
		{
			result= rs.getInt(1); 
		}
		rs.close();
		ps.clearParameters();
		ps.close();
		return result;
	}

	public int getHeight(int positionId) throws Exception {
		Connection conn=null;
		try
		{
			conn = DBUtils.getInstance().getConnection();
			return getHeight(positionId, conn);
		}catch(Exception e)
		{
			throw e;
		}finally 
		{
			DBUtils.closeConnection(conn);
		}
		
	}

}
